<!DOCTYPE html>












  


<html class="theme-next mist use-motion" lang="en">
<head>
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=2">
<meta name="theme-color" content="#222">






  
  
    
    
  <script src="/lib/pace/pace.min.js?v=1.0.2"></script>
  <link href="/lib/pace/pace-theme-minimal.min.css?v=1.0.2" rel="stylesheet">







<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">






















<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/css/main.css?v=6.3.0" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=6.3.0">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/imagesfavicon.ico?v=6.3.0">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/imagesfavicon.ico?v=6.3.0">


  <link rel="mask-icon" href="/images/logo.svg?v=6.3.0" color="#222">









<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Mist',
    version: '6.3.0',
    sidebar: {"position":"left","display":"hide","offset":12,"b2t":false,"scrollpercent":true,"onmobile":false},
    fancybox: false,
    fastclick: false,
    lazyload: false,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>
  <meta name="description" content="Preface when there is rain neither sun nor moon, the people actually do not think so.  Perhaps there are seasonal climate not to be cold rain, let the sun or the cool side. Has the rain the night is o">
<meta name="keywords" content="Mysql,Database">
<meta property="og:type" content="article">
<meta property="og:title" content="The solution of Databases Query problems(Fifth)">
<meta property="og:url" content="https://absentm.github.io/2016/05/14/The-solution-of-Databases-Query-problems-Fifth/index.html">
<meta property="og:site_name" content="AbsentM&#39;s Notes">
<meta property="og:description" content="Preface when there is rain neither sun nor moon, the people actually do not think so.  Perhaps there are seasonal climate not to be cold rain, let the sun or the cool side. Has the rain the night is o">
<meta property="og:locale" content="en">
<meta property="og:image" content="http://img.blog.csdn.net/20160514111536892">
<meta property="og:image" content="http://img.blog.csdn.net/20160514115213402">
<meta property="og:image" content="http://img.blog.csdn.net/20160514124128083">
<meta property="og:image" content="http://img.blog.csdn.net/20160514141548373">
<meta property="og:image" content="http://img.blog.csdn.net/20160514141848936">
<meta property="og:image" content="http://img.blog.csdn.net/20160514171442001">
<meta property="og:image" content="http://img.blog.csdn.net/20160514171518586">
<meta property="og:image" content="http://img.blog.csdn.net/20160514171555665">
<meta property="og:image" content="http://img.blog.csdn.net/20160514171702269">
<meta property="og:image" content="http://img.blog.csdn.net/20160514171931932">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172008524">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172144354">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172236840">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172213136">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172533437">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172710419">
<meta property="og:image" content="http://img.blog.csdn.net/20160514172834435">
<meta property="og:updated_time" content="2017-11-21T11:44:44.747Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="The solution of Databases Query problems(Fifth)">
<meta name="twitter:description" content="Preface when there is rain neither sun nor moon, the people actually do not think so.  Perhaps there are seasonal climate not to be cold rain, let the sun or the cool side. Has the rain the night is o">
<meta name="twitter:image" content="http://img.blog.csdn.net/20160514111536892">






  <link rel="canonical" href="https://absentm.github.io/2016/05/14/The-solution-of-Databases-Query-problems-Fifth/">



<script type="text/javascript" id="page.configurations">
  CONFIG.page = {
    sidebar: "",
  };
</script>

  <title>The solution of Databases Query problems(Fifth) | AbsentM's Notes</title>
  






  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?021d64369b3c401c47c6ab7aa56f7074";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




  <noscript>
  <style type="text/css">
    .use-motion .motion-element,
    .use-motion .brand,
    .use-motion .menu-item,
    .sidebar-inner,
    .use-motion .post-block,
    .use-motion .pagination,
    .use-motion .comments,
    .use-motion .post-header,
    .use-motion .post-body,
    .use-motion .collection-title { opacity: initial; }

    .use-motion .logo,
    .use-motion .site-title,
    .use-motion .site-subtitle {
      opacity: initial;
      top: initial;
    }

    .use-motion {
      .logo-line-before i { left: initial; }
      .logo-line-after i { right: initial; }
    }
  </style>
</noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="en">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta">
    

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">AbsentM's Notes</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
    
  </div>

  <div class="site-nav-toggle">
    <button aria-label="Toggle navigation bar">
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>


<nav class="site-nav">
  
    <ul id="menu" class="menu">
      
        
        
        
          
          <li class="menu-item menu-item-home">
    <a href="/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-home"></i> <br>Home</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-categories">
    <a href="/categories/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-th"></i> <br>Categories</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-archives">
    <a href="/archives/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-archive"></i> <br>Archives</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-tags">
    <a href="/tags/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-tags"></i> <br>Tags</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-about">
    <a href="/about/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-user"></i> <br>About</a>
  </li>

      
      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br>Search</a>
        </li>
      
    </ul>
  

  
    

  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off" placeholder="Searching..." spellcheck="false" type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



  



</div>
    </header>

    

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  
    <div class="reading-progress-bar"></div>
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://absentm.github.io/2016/05/14/The-solution-of-Databases-Query-problems-Fifth/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="AbsentM">
      <meta itemprop="description" content="Avenue to Jane!">
      <meta itemprop="image" content="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/googlrPhoto.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="AbsentM's Notes">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">The solution of Databases Query problems(Fifth)
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">

            
            
            

            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">Posted on</span>
              

              
                
              

              <time title="Created: 2016-05-14 17:53:22" itemprop="dateCreated datePublished" datetime="2016-05-14T17:53:22+08:00">2016-05-14</time>
            

            
              

              
                
                <span class="post-meta-divider">|</span>
                

                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                
                  <span class="post-meta-item-text">Edited on</span>
                
                <time title="Modified: 2017-11-21 19:44:44" itemprop="dateModified" datetime="2017-11-21T19:44:44+08:00">2017-11-21</time>
              
            
          </span>

          
            <span class="post-category">
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">In</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing"><a href="/categories/Database-Notes/" itemprop="url" rel="index"><span itemprop="name">Database Notes</span></a></span>

                
                
              
            </span>
          

          
            
              <span class="post-comments-count">
                <span class="post-meta-divider">|</span>
                <span class="post-meta-item-icon">
                  <i class="fa fa-comment-o"></i>
                </span>
                <a href="/2016/05/14/The-solution-of-Databases-Query-problems-Fifth/#comments" itemprop="discussionUrl">
                
                  <span class="post-comments-count disqus-comment-count" data-disqus-identifier="2016/05/14/The-solution-of-Databases-Query-problems-Fifth/" itemprop="commentCount"></span>
                </a>
              </span>
            
          

          
          
             <span id="/2016/05/14/The-solution-of-Databases-Query-problems-Fifth/" class="leancloud_visitors" data-flag-title="The solution of Databases Query problems(Fifth)">
               <span class="post-meta-divider">|</span>
               <span class="post-meta-item-icon">
                 <i class="fa fa-eye"></i>
               </span>
               
                 <span class="post-meta-item-text">Views: </span>
               
                 <span class="leancloud-visitors-count"></span>
             </span>
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h2 id="Preface"><a href="#Preface" class="headerlink" title="Preface"></a>Preface</h2><blockquote>
<p>when there is rain neither sun nor moon, the people actually do not think so. </p>
<p>Perhaps there are seasonal climate not to be cold rain, let the sun or the cool side.</p>
<p>Has the rain the night is otherwise a moonlit night no flavor. </p>
<p>Sometimes not reminiscent of Li Shangyin “when he cut a total of west window candle, but then when the famous hope of reunion among friends”</p>
</blockquote>
<p>In the rain, fom the sky come the silk notes; here, quiet and comfortable.</p>
<p><img src="http://img.blog.csdn.net/20160514111536892" alt="in rain"></p>
<hr>
<h2 id="Presentation"><a href="#Presentation" class="headerlink" title="Presentation"></a>Presentation</h2><p>In this page, we will show the homework of the course of the Databases Technology in CMU. All of the questions we tested in the postgresql.</p>
<p>This section mainly is to practice the <strong>query operation of the SQL</strong>, include <strong>SQL view, index,</strong> etc. We also sure that all the sqls will pass in the mysql, sqlite… ,  this article also will provide the Chinese version in detail.</p>
<p><a href="https://absentm.github.io/2016/05/14/%E6%95%B0%E6%8D%AE%E5%BA%93%E9%A2%98%E7%9B%AE%E6%95%B4%E7%90%86%E5%8F%8A%E8%AF%A6%E8%A7%A3%E4%B8%AD%E6%96%87%E7%89%88%EF%BC%88%E4%BA%94%EF%BC%89/">中文版</a></p>
<hr>
<h2 id="Question-Details"><a href="#Question-Details" class="headerlink" title="Question Details"></a>Question Details</h2><p>In this homework you will have to write SQL queries to answer questions on a movie dataset, about movies and actors. The database contains two tables:</p>
<p>• movies(<em>mid</em>, title, year, num ratings, rating) , Primary key: mid<br>• play in(<em>mid, name</em>, cast position),  Primary key: mid, name</p>
<p>The tables contain the obvious information: which actor played in what movie, at what position; for each movie, we have the title (eg., ’Gone with the wind’), year of production, count of rating reviews it received, and the average score of those ratings (a float in the range 0 to 10, with ’10’ meaning ’excellent’). </p>
<hr>
<p>We will use <strong>Postgres</strong>, which is installed in the your own machines.</p>
<p><strong>Question 1: Warm-up queries . . . . . . . . . . . . . . . . . . . . . . . . . [5 points]</strong><br>(a) [2 points] Print all actors in the movie Quantum of Solace, sorted by cast position. Print only their names.<br>(b) [3 points] Print all movie titles that were released in 2002, with rating larger than 8 and with more than one rating (num ratings &gt; 1).</p>
<hr>
<p><strong>Question 2: Find the star’s movies . . . . . . . . . . . . . . . . . . . . [5 points]</strong><br>(a) [5 points] Print movie titles where Sean Connery was the star (i.e. he had position 1 in the cast). Sort the movie titles alphabetically.</p>
<hr>
<p><strong>Question 3: Popular actors . . . . . . . . . . . . . . . . . . . . . . . . . . [15 points]</strong><br>(a) [8 points] We want to find the actors of the highest quality. We define their quality as the weighted average of the ratings of the movies they have played in (regardless of cast position), using the number of ratings for each movie as the<br>weight. In other words, we define the quality for a particular actor as</p>
<p><img src="http://img.blog.csdn.net/20160514115213402" alt="计算公式"></p>
<p>Print the names of the top 5 actors, according to the above metric. Break ties alphabetically.</p>
<p>(b) [7 points] Now we want to find the 5 most popular actors, in terms of number of ratings (regardless of positive or negative popularity). I.e, if actor ‘Smith’ played in 2 movies, with num ratings 10 and 15, then Smith’s popularity is 25 (=10+15). Print the top 5 actor names according to popularity. Again, break ties alphabetically.</p>
<hr>
<p><strong>Question 4: Most controversial actor . . . . . . . . . . . . . . . . [10 points]</strong><br>(a) [10 points] We want to find the most controversial actor. As a measure of controversy, we define the maximum difference between the ratings of two movies that an actor has played in (regardless of cast position). That is, if actor ‘Smith’ played in a movie that got rating=1.2, and another that got rating=9.5, and all the other movies he played in, obtained scores within that range, then Smith’s contoversy score is 9.5-1.2= 8.3. Print the name of the top-most controversial actor - again, if there is a tie in first place, break it alphabetically.</p>
<hr>
<p><strong>Question 5: The minions . . . . . . . . . . . . . . . . . . . . . . . . . . . . [20 points]</strong><br>(a) [20 points] Find the “minions” of Annette Nicole: Print the names of actors who only played in movies with her and never without her. The answer should not contain the name of Annette Nicole. Order the names alphabetically.</p>
<hr>
<p><strong>Question 6: High productivity . . . . . . . . . . . . . . . . . . . . . . . . [5 points]</strong><br>(a) [5 points] Find the top 2 most productive years (by number of movies produced). Solve ties by preferring chronologically older years, and print only the years.</p>
<hr>
<p><strong>Question 7: Movies with similar cast . . . . . . . . . . . . . . . . [15 points]</strong><br>(a) [8 points] Print the count of distinct pairs of movies that have at least one actor in common (ignoring cast position). Exclude self-pairs, and mirror-pairs.<br>(b) [7 points] Print the count of distinct pairs of moves that have at least two actors in common (again, ignoring cast position). Again, exclude self-pairs, and mirror pairs.</p>
<hr>
<p><strong>Question 8: Skyline query . . . . . . . . . . . . . . . . . . . . . . . . . . . [25 points]</strong><br>(a) [25 points] We want to find a set of movies that have both high popularity (ie, high num ratings) as well as high quality (rating). No single movie may achieve both - in which case, we want the so-called Skyline query 2 . More specifically, we want all movies that are not “dominated” by any other movie:</p>
<p><strong>Definition of domination :</strong> <em>Movie “A” dominates movie “B” if movie “A” wins over movie “B”, on both criteria, or wins on one, and ties on the rest.</em></p>
<p>Figure 1 gives a pictorial example: the solid dots (’A’, ’D’, ’F’) are not dominated by any other dot, and thus form the skyline. All other dots are dominated by at least one other dot: e.g., dot ’B’ is dominated by dot ’A’, being inside the shaded rectangle that has ’A’ as the upper-right corner.</p>
<p><img src="http://img.blog.csdn.net/20160514124128083" alt="figure 1"></p>
<p>Figure 1: Illustration of Skyline and domination : ’A’ dominates all points in the shaded rectangle; ’A’, ’D’ and ’F’ form the skyline of this cloud of points.</p>
<p>Given the above description, print the title of all the movies on the skyline, along with the rating and the number of ratings.</p>
<hr>
<h2 id="Answer"><a href="#Answer" class="headerlink" title="Answer"></a>Answer</h2><p>we give the Postgres version in detail, we will see you can tranfer it easily in mysql or sqlite.</p>
<h3 id="Initialization"><a href="#Initialization" class="headerlink" title="Initialization:"></a>Initialization:</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">## drop the table if exists</span><br><span class="line">drop table if exists movies cascade;   </span><br><span class="line">drop table if exists play_in cascade;</span><br><span class="line"></span><br><span class="line">## create tables movies and play_in</span><br><span class="line">create table movies (</span><br><span class="line">mid integer PRIMARY KEY, </span><br><span class="line">title varchar(200),</span><br><span class="line">year integer,</span><br><span class="line">num_ratings integer, </span><br><span class="line">rating real);</span><br><span class="line"></span><br><span class="line">create table play_in (</span><br><span class="line">mid integer references movies, </span><br><span class="line">name varchar(100), </span><br><span class="line">cast_position integer, </span><br><span class="line">PRIMARY KEY(mid, name));</span><br><span class="line"></span><br><span class="line">create index mid on movies(mid);</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Insert-Values"><a href="#Insert-Values" class="headerlink" title="Insert Values"></a>Insert Values</h3><p>Insert into some values into the table movies and play_in,<br>you will find the datas just in the follow links in my 360 yunFiles:</p>
<p><a href="https://yunpan.cn/cSfLzxQApRXSi" target="_blank" rel="noopener">https://yunpan.cn/cSfLzxQApRXSi</a>     password: f3ab</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">## use &quot;copy&quot; in Postgres</span><br><span class="line">\copy movies from &apos;~/data/movie_processed.dat&apos;;</span><br><span class="line">\copy play_in from &apos;~/data/movie_actor_processed.dat&apos;;</span><br><span class="line"></span><br><span class="line">## use &quot;load data local infile&quot; in Mysql</span><br><span class="line">load data local infile &quot;~/data/movie_processed.dat&quot; into table movies FIELDS TERMINATED BY &apos;\t&apos;;</span><br><span class="line"></span><br><span class="line">load data local infile &quot;~/data/movie_actor_processed.dat&quot; into table play_in FIELDS TERMINATED BY &apos;\t&apos;;</span><br><span class="line"></span><br><span class="line">## if you find load infile errors in mysql just like this:</span><br><span class="line">## ERROR 1148 (42000): The used command is not allowed with this MySQL version</span><br><span class="line">## please grant FILE authority in Mysql just use this comman: &quot;mysql -u root -p tablename --local-infile=1&quot; to login databases;</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514141548373" alt="ctreate and copy datas1"></p>
<p><img src="http://img.blog.csdn.net/20160514141848936" alt="ctreate and copy datas2"></p>
<hr>
<h3 id="Solution-1"><a href="#Solution-1" class="headerlink" title="Solution 1"></a>Solution 1</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br></pre></td><td class="code"><pre><span class="line">(a) SELECT name FROM play_in p, movies m</span><br><span class="line">WHERE p.mid = m.mid and m.title=’Quantum of Solace’</span><br><span class="line">ORDER BY p.cast_position;</span><br><span class="line"></span><br><span class="line">## (a) Result just like this:</span><br><span class="line"></span><br><span class="line">name</span><br><span class="line">------------------------------</span><br><span class="line">Daniel Craig</span><br><span class="line">Olga Kurylenko</span><br><span class="line">Mathieu Amalric</span><br><span class="line">Judi Dench</span><br><span class="line">Giancarlo Giannini</span><br><span class="line">Gemma Arterton</span><br><span class="line">Jeffrey Wright</span><br><span class="line">David Harbour</span><br><span class="line">Jesper Christensen</span><br><span class="line">Anatole Taubman</span><br><span class="line">Rory Kinnear</span><br><span class="line">Tim Pigott-Smith</span><br><span class="line">Fernando Guillen-Cuervo</span><br><span class="line">Jesus Ochoa</span><br><span class="line">Glenn Foster</span><br><span class="line">Paul Ritter</span><br><span class="line">Simon Kassianides</span><br><span class="line">Stana Katic</span><br><span class="line">Lucrezia Lante della Rove...</span><br><span class="line">Neil Jackson</span><br><span class="line">Oona Chaplin</span><br><span class="line">(21 rows)</span><br><span class="line"></span><br><span class="line">(b) SELECT title FROM movies</span><br><span class="line">WHERE year = 2002 and rating&gt;8 and num_ratings&gt;1;</span><br><span class="line"></span><br><span class="line">## (b) Result just like this:</span><br><span class="line">title</span><br><span class="line">---------------------------------------</span><br><span class="line">The Lord of the Rings: The Two Towers</span><br><span class="line">Cidade de Deus</span><br><span class="line">Mou gaan dou</span><br><span class="line">(3 rows)</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 1 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514171442001" alt="3"></p>
<p><img src="http://img.blog.csdn.net/20160514171518586" alt="3.1"></p>
<p><img src="http://img.blog.csdn.net/20160514171555665" alt="4"></p>
<hr>
<h3 id="Solution-2"><a href="#Solution-2" class="headerlink" title="Solution 2"></a>Solution 2</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">SELECT title from movies m, play_in p</span><br><span class="line">WHERE m.mid = p.mid and </span><br><span class="line">name = ’Sean Connery’ and </span><br><span class="line">cast_position = 1</span><br><span class="line">ORDER BY title;</span><br><span class="line"></span><br><span class="line">## Result just like this:</span><br><span class="line">title</span><br><span class="line">---------------------------------------</span><br><span class="line">Der Name der Rose</span><br><span class="line">Diamonds Are Forever</span><br><span class="line">Dr. No</span><br><span class="line">Entrapment</span><br><span class="line">Finding Forrester</span><br><span class="line">First Knight</span><br><span class="line">From Russia with Love</span><br><span class="line">Goldfinger</span><br><span class="line">Never Say Never Again</span><br><span class="line">The Hunt for Red October</span><br><span class="line">The League of Extraordinary Gentlemen</span><br><span class="line">Thunderball</span><br><span class="line">You Only Live Twice</span><br><span class="line">(13 rows)</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 2 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514171702269" alt="5"></p>
<hr>
<h3 id="Solution-3"><a href="#Solution-3" class="headerlink" title="Solution 3"></a>Solution 3</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line">(a) DROP VIEW IF EXISTS WeigthedRatings;</span><br><span class="line"></span><br><span class="line">CREATE VIEW WeightedRatings AS</span><br><span class="line">SELECT name, SUM(rating*num_ratings)/SUM(num_ratings) AS WeightedRating</span><br><span class="line">FROM movies m, play_in p WHERE m.mid = p.mid GROUP BY(name);</span><br><span class="line"></span><br><span class="line">SELECT name FROM WeightedRatings</span><br><span class="line">ORDER BY</span><br><span class="line">WeightedRating DESC, name ASC LIMIT 5;</span><br><span class="line"></span><br><span class="line">## (a) Result just like this:</span><br><span class="line">name</span><br><span class="line">-----------------------</span><br><span class="line">Adam Kalesperis</span><br><span class="line">Aidan Feore</span><br><span class="line">Aleksandr Kajdanovsky</span><br><span class="line">Alexander Kaidanovsky</span><br><span class="line">Alisa Frejndlikh</span><br><span class="line">(5 rows)</span><br><span class="line"></span><br><span class="line">(b) DROP VIEW IF EXISTS ActorSumRatings;</span><br><span class="line"></span><br><span class="line">CREATE VIEW ActorSumRatings AS</span><br><span class="line">SELECT name, SUM(num_ratings) as popularity</span><br><span class="line">FROM play_in p, movies m</span><br><span class="line">WHERE p.mid = m.mid</span><br><span class="line">GROUP BY name;</span><br><span class="line"></span><br><span class="line">SELECT name from ActorSumRatings</span><br><span class="line">ORDER BY popularity DESC, name ASC LIMIT 5;</span><br><span class="line"></span><br><span class="line">## (b) Result just like this:</span><br><span class="line">name</span><br><span class="line">----------------------</span><br><span class="line">Johnny Depp</span><br><span class="line">Alan Rickman</span><br><span class="line">Orlando Bloom</span><br><span class="line">Helena Bonham Carter</span><br><span class="line">Matt Damon</span><br><span class="line">(5 rows)</span><br></pre></td></tr></table></figure>
<p>The flowing images show the test solution 3 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514171931932" alt="6"></p>
<p><img src="http://img.blog.csdn.net/20160514172008524" alt="7"></p>
<hr>
<h3 id="Solution-4"><a href="#Solution-4" class="headerlink" title="Solution 4"></a>Solution 4</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">DROP VIEW IF EXISTS RatingGap;</span><br><span class="line"></span><br><span class="line">CREATE VIEW RatingGap AS</span><br><span class="line">SELECT p1.name, MAX(ABS(m1.rating-m2.rating)) as Gap</span><br><span class="line">FROM play_in p1, play_in p2, movies m1, movies m2</span><br><span class="line">WHERE p1.mid = m1.mid and </span><br><span class="line">p2.mid = m2.mid and </span><br><span class="line">p1.name = p2.name</span><br><span class="line">GROUP BY(p1.name);</span><br><span class="line"></span><br><span class="line">SELECT name </span><br><span class="line">FROM RatingGap </span><br><span class="line">ORDER BY(Gap) DESC LIMIT 1;</span><br><span class="line"></span><br><span class="line">## Result just like this:</span><br><span class="line">name</span><br><span class="line">---------------</span><br><span class="line">John Travolta</span><br><span class="line">(1 row)</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 4 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514172144354" alt="8.1"></p>
<p><img src="http://img.blog.csdn.net/20160514172236840" alt="8.3"></p>
<hr>
<h3 id="Solution-5"><a href="#Solution-5" class="headerlink" title="Solution 5"></a>Solution 5</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br></pre></td><td class="code"><pre><span class="line">DROP VIEW IF EXISTS MastersMovies CASCADE;</span><br><span class="line"></span><br><span class="line">CREATE VIEW MastersMovies AS</span><br><span class="line">SELECT m.mid,m.title FROM movies m, play_in p</span><br><span class="line">WHERE m.mid = p.mid and p.name = ’Annette Nicole’;</span><br><span class="line"></span><br><span class="line">DROP VIEW IF EXISTS CoActors;</span><br><span class="line"></span><br><span class="line">CREATE VIEW CoActors AS</span><br><span class="line">SELECT DISTINCT name FROM MastersMovies m , play_in p</span><br><span class="line">WHERE p.mid = m.mid;</span><br><span class="line"></span><br><span class="line">DROP VIEW IF EXISTS Combinations;</span><br><span class="line"></span><br><span class="line">CREATE VIEW Combinations AS</span><br><span class="line">SELECT name,mid FROM MastersMovies , CoActors;</span><br><span class="line"></span><br><span class="line">DROP VIEW IF EXISTS NonExistent;</span><br><span class="line"></span><br><span class="line">CREATE VIEW NonExistent AS</span><br><span class="line">SELECT * FROM Combinations</span><br><span class="line">EXCEPT (SELECT name, mid FROM play_in);</span><br><span class="line"></span><br><span class="line">DROP VIEW IF EXISTS PotentialResults;</span><br><span class="line"></span><br><span class="line">CREATE VIEW PotentialResults AS</span><br><span class="line">SELECT * from CoActors</span><br><span class="line">EXCEPT (SELECT distinct(name) FROM NonExistent);</span><br><span class="line"></span><br><span class="line">DROP VIEW IF EXISTS NotMastersMovies;</span><br><span class="line"></span><br><span class="line">CREATE VIEW NotMastersMovies AS</span><br><span class="line">SELECT m.mid FROM movies m</span><br><span class="line">EXCEPT (SELECT mid FROM MastersMovies);</span><br><span class="line"></span><br><span class="line">SELECT * from PotentialResults</span><br><span class="line">WHERE name not in </span><br><span class="line">(SELECT name</span><br><span class="line">FROM play_in p, NotMastersMovies m</span><br><span class="line">WHERE m.mid = p.mid</span><br><span class="line">UNION SELECT ’Annette Nicole’</span><br><span class="line">) ORDER BY name;</span><br><span class="line"></span><br><span class="line">## Result just like this:</span><br><span class="line">name</span><br><span class="line">-----------------</span><br><span class="line">Christian Perry</span><br><span class="line">(1 row)</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 5 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514172213136" alt="8.2"></p>
<hr>
<h3 id="Solution-6"><a href="#Solution-6" class="headerlink" title="Solution 6"></a>Solution 6</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">DROP VIEW IF EXISTS MoviesPerYear;</span><br><span class="line"></span><br><span class="line">CREATE VIEW MoviesPerYear AS</span><br><span class="line">SELECT year, COUNT(title) num_movies</span><br><span class="line">FROM MOVIES GROUP BY(year);</span><br><span class="line"></span><br><span class="line">SELECT year from MoviesPerYear </span><br><span class="line">ORDER BY num_movies DESC LIMIT 2;</span><br><span class="line"></span><br><span class="line">## Result just like this:</span><br><span class="line">year</span><br><span class="line">------</span><br><span class="line">2006</span><br><span class="line">2007</span><br><span class="line">(2 rows)</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 6 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514172533437" alt="9"></p>
<hr>
<h3 id="Solution-7"><a href="#Solution-7" class="headerlink" title="Solution 7"></a>Solution 7</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line">(a) SELECT COUNT(*) FROM </span><br><span class="line">(SELECT DISTINCT m1.mid, m2.mid</span><br><span class="line"> FROM movies m1, movies m2, play_in p1, play_in p2</span><br><span class="line"> WHERE m1.mid &gt; m2.mid and</span><br><span class="line"> m1.mid = p1.mid and</span><br><span class="line"> m2.mid = p2.mid and </span><br><span class="line"> p1.name = p2.name) </span><br><span class="line">AS count;</span><br><span class="line"></span><br><span class="line">## (a) Result just like this:</span><br><span class="line">count</span><br><span class="line">--------</span><br><span class="line">104846</span><br><span class="line">(1 row)</span><br><span class="line"></span><br><span class="line">(b) SELECT COUNT(*) FROM </span><br><span class="line">(SELECT DISTINCT m1.mid, m2.mid</span><br><span class="line">FROM movies m1, movies m2, play_in p1,</span><br><span class="line">play_in p2, play_in p3, play_in p4</span><br><span class="line">WHERE m1.mid &gt; m2.mid and </span><br><span class="line">m1.mid = p1.mid and </span><br><span class="line">m2.mid = p2.mid and</span><br><span class="line">m1.mid = p3.mid and </span><br><span class="line">m2.mid = p4.mid and </span><br><span class="line">p2.name &lt;&gt; p4.name and </span><br><span class="line">p1.name = p2.name and </span><br><span class="line">p3.name = p4.name) </span><br><span class="line">AS count;</span><br><span class="line"></span><br><span class="line">## (b) Result just like this:</span><br><span class="line">count</span><br><span class="line">-------</span><br><span class="line">6845</span><br><span class="line">(1 row)</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 7 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514172710419" alt="11"></p>
<hr>
<h3 id="Solution-8"><a href="#Solution-8" class="headerlink" title="Solution 8"></a>Solution 8</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">DROP VIEW IF EXISTS Dominated;</span><br><span class="line"></span><br><span class="line">CREATE VIEW Dominated AS</span><br><span class="line">SELECT DISTINCT m2.mid, m2.title,m2.num_ratings, m2.rating</span><br><span class="line">FROM movies m1, movies m2</span><br><span class="line">WHERE m2.rating&lt;=m1.rating and m2.num_ratings&lt;=m1.num_ratings and </span><br><span class="line">NOT (m2.rating = m1.rating and m2.num_ratings=m1.num_ratings);</span><br><span class="line"></span><br><span class="line">SELECT title,num_ratings,rating </span><br><span class="line">FROM movies</span><br><span class="line">EXCEPT (SELECT title,num_ratings,rating FROM Dominated);</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 8 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160514172834435" alt="12"></p>
<hr>
<h2 id="Reference"><a href="#Reference" class="headerlink" title="Reference"></a>Reference</h2><p>[1] <a href="http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html" target="_blank" rel="noopener">http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html</a><br>[2] <a href="http://www.postgresql.org/docs/" target="_blank" rel="noopener">http://www.postgresql.org/docs/</a><br>[3] <a href="http://www.cs.cmu.edu/~epapalex/15415S14/PostgreSQLReadme.htm" target="_blank" rel="noopener">http://www.cs.cmu.edu/~epapalex/15415S14/PostgreSQLReadme.htm</a><br>[4] <a href="http://www.cs.cmu.edu/~christos/courses/" target="_blank" rel="noopener">http://www.cs.cmu.edu/~christos/courses/</a><br>[5] <a href="http://blog.chinaunix.net/uid-20685819-id-4267454.html" target="_blank" rel="noopener">http://blog.chinaunix.net/uid-20685819-id-4267454.html</a></p>
      
    </div>

    

    
    
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center">
  <div>Enjoy it ? Donate me !  求鼓励，求支持！</div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>Donate</span>
  </button>
  <div id="QR" style="display: none">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/wechat.png" alt="AbsentM WeChat Pay">
        <p>WeChat Pay</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/alipay.png" alt="AbsentM Alipay">
        <p>Alipay</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/Mysql/" rel="tag"># Mysql</a>
          
            <a href="/tags/Database/" rel="tag"># Database</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2016/04/16/数据库题目整理及详解（四）/" rel="next" title="数据库题目整理及详解（四）">
                <i class="fa fa-chevron-left"></i> 数据库题目整理及详解（四）
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2016/05/14/数据库题目整理及详解中文版（五）/" rel="prev" title="数据库题目整理及详解中文版（五）">
                数据库题目整理及详解中文版（五） <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>


  </div>


          </div>
          

  
    <div class="comments" id="comments">
      <div id="disqus_thread">
        <noscript>
          Please enable JavaScript to view the
          <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a>
        </noscript>
      </div>
    </div>

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            Table of Contents
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            Overview
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image" src="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/googlrPhoto.png" alt="AbsentM">
            
              <p class="site-author-name" itemprop="name">AbsentM</p>
              <p class="site-description motion-element" itemprop="description">Avenue to Jane!</p>
          </div>

          
            <nav class="site-state motion-element">
              
                <div class="site-state-item site-state-posts">
                
                  <a href="/archives/">
                
                    <span class="site-state-item-count">57</span>
                    <span class="site-state-item-name">posts</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-categories">
                  <a href="/categories/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">8</span>
                    <span class="site-state-item-name">categories</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-tags">
                  <a href="/tags/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">37</span>
                    <span class="site-state-item-name">tags</span>
                  </a>
                </div>
              
            </nav>
          

          

          
            <div class="links-of-author motion-element">
              
                <span class="links-of-author-item">
                  <a href="https://github.com/absentm" target="_blank" title="GitHub"><i class="fa fa-fw fa-github"></i>GitHub</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="https://twitter.com/absentm123" target="_blank" title="Twitter"><i class="fa fa-fw fa-twitter"></i>Twitter</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="http://weibo.com/dmicrocosmos" target="_blank" title="Weibo"><i class="fa fa-fw fa-weibo"></i>Weibo</a>
                  
                </span>
              
            </div>
          

          
          

          
          
            <div class="links-of-blogroll motion-element links-of-blogroll-inline">
              <div class="links-of-blogroll-title">
                <i class="fa fa-fw fa-link"></i>
                Links
              </div>
              <ul class="links-of-blogroll-list">
                
                  <li class="links-of-blogroll-item">
                    <a href="https://www.hanhanfilm.com/" title="憨憨电影" target="_blank">憨憨电影</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.fengxiaochuang.com/" title="晓闯博客" target="_blank">晓闯博客</a>
                  </li>
                
              </ul>
            </div>
          

          
            
          
          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#Preface"><span class="nav-number">1.</span> <span class="nav-text">Preface</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Presentation"><span class="nav-number">2.</span> <span class="nav-text">Presentation</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Question-Details"><span class="nav-number">3.</span> <span class="nav-text">Question Details</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Answer"><span class="nav-number">4.</span> <span class="nav-text">Answer</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#Initialization"><span class="nav-number">4.1.</span> <span class="nav-text">Initialization:</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Insert-Values"><span class="nav-number">4.2.</span> <span class="nav-text">Insert Values</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-1"><span class="nav-number">4.3.</span> <span class="nav-text">Solution 1</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-2"><span class="nav-number">4.4.</span> <span class="nav-text">Solution 2</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-3"><span class="nav-number">4.5.</span> <span class="nav-text">Solution 3</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-4"><span class="nav-number">4.6.</span> <span class="nav-text">Solution 4</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-5"><span class="nav-number">4.7.</span> <span class="nav-text">Solution 5</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-6"><span class="nav-number">4.8.</span> <span class="nav-text">Solution 6</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-7"><span class="nav-number">4.9.</span> <span class="nav-text">Solution 7</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-8"><span class="nav-number">4.10.</span> <span class="nav-text">Solution 8</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Reference"><span class="nav-number">5.</span> <span class="nav-text">Reference</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; 2015 – <span itemprop="copyrightYear">2019</span>
  <span class="with-love" id="animate">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">AbsentM</span>

  

  
</div>




  <div class="powered-by">Powered by <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> v3.7.1</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">Theme – <a class="theme-link" target="_blank" href="https://theme-next.org">NexT.Mist</a> v6.3.0</div>
        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

  
    <span class="site-uv" title="Total Visitors">
      <i class="fa fa-user"></i>
      <span class="busuanzi-value" id="busuanzi_value_site_uv"></span>
    </span>
  

  
    <span class="site-pv" title="Total Views">
      <i class="fa fa-eye"></i>
      <span class="busuanzi-value" id="busuanzi_value_site_pv"></span>
    </span>
  
</div>









        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
          <span id="scrollpercent"><span>0</span>%</span>
        
      </div>
    

    
	
    

    
      <div>
        <!-- Go to www.addthis.com/dashboard to customize your tools -->
<div class="addthis_inline_share_toolbox">
  <script type="text/javascript" src="//s7.addthis.com/js/300/addthis_widget.js#pubid=ra-58d66725c4aec37d" async="async"></script>
</div>

      </div>
    
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>
























  



  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/reading_progress/reading_progress.js"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=6.3.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=6.3.0"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=6.3.0"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=6.3.0"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=6.3.0"></script>



  

  
    <script id="dsq-count-scr" src="https://absentm.disqus.com/count.js" async></script>
  

  
    <script type="text/javascript">
      var disqus_config = function () {
        this.page.url = 'https://absentm.github.io/2016/05/14/The-solution-of-Databases-Query-problems-Fifth/';
        this.page.identifier = '2016/05/14/The-solution-of-Databases-Query-problems-Fifth/';
        this.page.title = 'The solution of Databases Query problems(Fifth)';
        };
      function loadComments () {
        var d = document, s = d.createElement('script');
        s.src = 'https://absentm.disqus.com/embed.js';
        s.setAttribute('data-timestamp', '' + +new Date());
        (d.head || d.body).appendChild(s);
      }
      
        $(function () {
          var offsetTop = $('#comments').offset().top - $(window).height();
          if (offsetTop <= 0) {
            // load directly when there's no a scrollbar
            loadComments();
          } else {
            $(window).on('scroll.disqus_scroll', function () {
              var scrollTop = document.documentElement.scrollTop;
              if (scrollTop >= offsetTop) {
                $(window).off('.disqus_scroll');
                loadComments();
              }
            });
          }
        });
      
    </script>
  












  

  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url);
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  

  
  <script src="https://cdn1.lncld.net/static/js/av-core-mini-0.6.4.js"></script>
  <script>AV.initialize("8gIMbJWMH7XprqgkEiPWXvuu-gzGzoHsz", "W9NRpyhllWph45XiJsEHVgct");</script>
  <script>
    function showTime(Counter) {
      var query = new AV.Query(Counter);
      var entries = [];
      var $visitors = $(".leancloud_visitors");

      $visitors.each(function () {
        entries.push( $(this).attr("id").trim() );
      });

      query.containedIn('url', entries);
      query.find()
        .done(function (results) {
          var COUNT_CONTAINER_REF = '.leancloud-visitors-count';

          if (results.length === 0) {
            $visitors.find(COUNT_CONTAINER_REF).text(0);
            return;
          }

          for (var i = 0; i < results.length; i++) {
            var item = results[i];
            var url = item.get('url');
            var time = item.get('time');
            var element = document.getElementById(url);

            $(element).find(COUNT_CONTAINER_REF).text(time);
          }
          for(var i = 0; i < entries.length; i++) {
            var url = entries[i];
            var element = document.getElementById(url);
            var countSpan = $(element).find(COUNT_CONTAINER_REF);
            if( countSpan.text() == '') {
              countSpan.text(0);
            }
          }
        })
        .fail(function (object, error) {
          console.log("Error: " + error.code + " " + error.message);
        });
    }

    function addCount(Counter) {
      var $visitors = $(".leancloud_visitors");
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();
      var query = new AV.Query(Counter);

      query.equalTo("url", url);
      query.find({
        success: function(results) {
          if (results.length > 0) {
            var counter = results[0];
            counter.fetchWhenSave(true);
            counter.increment("time");
            
            counter.save(null, {
              success: function(counter) {
                
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(counter.get('time'));
                
              },
              error: function(counter, error) {
                console.log('Failed to save Visitor num, with error message: ' + error.message);
              }
            });
          } else {
            
              var newcounter = new Counter();
              /* Set ACL */
              var acl = new AV.ACL();
              acl.setPublicReadAccess(true);
              acl.setPublicWriteAccess(true);
              newcounter.setACL(acl);
              /* End Set ACL */
              newcounter.set("title", title);
              newcounter.set("url", url);
              newcounter.set("time", 1);
              newcounter.save(null, {
                success: function(newcounter) {
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(newcounter.get('time'));
                },
                error: function(newcounter, error) {
                  console.log('Failed to create');
                }
              });
            
          }
        },
        error: function(error) {
          console.log('Error:' + error.code + " " + error.message);
        }
      });
    }

    $(function() {
      var Counter = AV.Object.extend("Counter");
      if ($('.leancloud_visitors').length == 1) {
        addCount(Counter);
      } else if ($('.post-title-link').length > 1) {
        showTime(Counter);
      }
    });
  </script>



  

  
<script>
(function(){
    var bp = document.createElement('script');
    var curProtocol = window.location.protocol.split(':')[0];
    if (curProtocol === 'https') {
        bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';        
    }
    else {
        bp.src = 'http://push.zhanzhang.baidu.com/push.js';
    }
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
})();
</script>


  
  

  
  

  


  
  

  

  

  

  

  

</body>
</html>